package com.keke.ado.bill;

import com.keke.ado.baseDao;
import com.keke.pojo.Bill;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class billDaoImpl implements billDao {

    public int add(Connection connection, Bill bill) throws Exception {
        PreparedStatement preparedStatement=null;
        int flag=0;
        if(connection!=null){
            String sql = "insert into smbms_bill (billCode,productName,productDesc," +
                    "productUnit,productCount,totalPrice,isPayment,providerId,createdBy,creationDate) " +
                    "values(?,?,?,?,?,?,?,?,?,?)";
            Object []params={bill.getBillCode(),bill.getProductName(),bill.getProductDesc(),bill.getProductUnit(),
                    bill.getProductCount(),bill.getTotalPrice(),bill.getIsPayment(),
                    bill.getId(),bill.getCreatedBy(),bill.getCreationDate()
            };
            flag= baseDao.execute(connection,preparedStatement,sql,params);
            baseDao.close(null,preparedStatement,null);
        }
        return flag;
    }

    public List<Bill> getBillList(Connection connection,Bill bill,int currentPageNo, int pageSize) throws Exception {
        PreparedStatement preparedStatement = null;
        List<Bill> billList = new ArrayList<Bill>();
        ResultSet resultSet = null;
        if(connection!=null){
            StringBuffer sql = new StringBuffer();
            sql.append("select b.*,p.proName as providerName from smbms_bill b, smbms_provider p where b.providerId = p.id");
            List<Object> list = new ArrayList<Object>();
            if(!StringUtils.isNullOrEmpty(bill.getProductName())){
                sql.append(" and productName = ?");
                list.add("%" + bill.getProductName()+"%");
            }
            if(bill.getProviderId()>0){
                sql.append(" and providerId = ?");
                list.add(bill.getProviderId());
            }
            if(bill.getIsPayment()>0){
                sql.append(" and isPayment = ?");
                list.add(bill.getIsPayment());
            }
            System.out.println("provideId ----> "+bill.getProviderId());
            System.out.println("ispayment ----> "+bill.getIsPayment());
            System.out.println("currentPageNo ----> "+currentPageNo );
            System.out.println("pageSize ----> "+pageSize );

            sql.append(" order by creationDate DESC limit ?,?");
            currentPageNo=(currentPageNo-1)*pageSize;
            list.add(currentPageNo);
            list.add(pageSize);

            Object []params = list.toArray();
            System.out.println("sql ------------> "+sql.toString());

            resultSet=baseDao.execute(connection,preparedStatement,resultSet,sql.toString(),params);
            while (resultSet.next()){
                Bill _bill = new Bill();
                _bill.setId(resultSet.getInt("id"));
                _bill.setBillCode(resultSet.getString("billCode"));
                _bill.setProductName(resultSet.getString("productName"));
                _bill.setProductDesc(resultSet.getString("productDesc"));
                _bill.setProductUnit(resultSet.getString("productUnit"));
                _bill.setProductCount(resultSet.getBigDecimal("productCount"));
                _bill.setTotalPrice(resultSet.getBigDecimal("totalPrice"));
                _bill.setIsPayment(resultSet.getInt("isPayment"));
                _bill.setId(resultSet.getInt("providerId"));
                _bill.setProviderName(resultSet.getString("providerName"));
                _bill.setCreationDate(resultSet.getTimestamp("creationDate"));
                _bill.setCreatedBy(resultSet.getInt("createdBy"));
                billList.add(_bill);
            }
            baseDao.close(null,preparedStatement,resultSet);
        }
        return billList;
    }

    public int deleteBillById(Connection connection, String delId) throws Exception {
        PreparedStatement preparedStatement=null;
        int flag=0;
        if(connection!=null){
            String sql = "delete from smbms_bill where id = ?";
            Object []params = {delId};
            flag = baseDao.execute(connection,preparedStatement,sql,params);
            baseDao.close(null,preparedStatement,null);
        }
        return flag;
    }

    public Bill getBillById(Connection connection, String id) throws Exception {
        Bill bill = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        if(connection!=null){
            String sql = "select b.*,p.proName as providerName from smbms_bill b, smbms_provider p "+
                    "where b.providerId = p.id and b.id = ?";
            Object []params = {id};
            resultSet=baseDao.execute(connection,preparedStatement,resultSet,sql,params);
            if (resultSet.next()) {
                bill = new Bill();
                bill.setId(resultSet.getInt("id"));
                bill.setBillCode(resultSet.getString("billCode"));
                bill.setProductName(resultSet.getString("productName"));
                bill.setProductDesc(resultSet.getString("productDesc"));
                bill.setProductUnit(resultSet.getString("productUnit"));
                bill.setProductCount(resultSet.getBigDecimal("productCount"));
                bill.setTotalPrice(resultSet.getBigDecimal("totalPrice"));
                bill.setIsPayment(resultSet.getInt("isPayment"));
                bill.setId(resultSet.getInt("providerId"));
                bill.setProviderName(resultSet.getString("providerName"));
                bill.setModifyBy(resultSet.getInt("modifyBy"));
                bill.setModifyDate(resultSet.getTimestamp("modifyDate"));
            }
            baseDao.close(null,preparedStatement,resultSet);
        }
        return bill;
    }

    public int modifyBill(Connection connection, Bill bill) throws Exception {
        int flag = 0;
        PreparedStatement preparedStatement = null;
        if(connection!=null){
            String sql = "update smbms_bill set productName=?," +
                    "productDesc=?,productUnit=?,productCount=?,totalPrice=?," +
                    "isPayment=?,providerId=?,modifyBy=?,modifyDate=? where id = ? ";
            Object []params = {bill.getProductName(), bill.getProductDesc(),
                    bill.getProductUnit(), bill.getProductCount(), bill.getTotalPrice(), bill.getIsPayment(),
                    bill.getId(), bill.getModifyBy(), bill.getModifyDate(), bill.getId()};
            flag = baseDao.execute(connection, preparedStatement, sql, params);
            baseDao.close(null,preparedStatement,null);
        }
        return flag;
    }

    public int getBillCountByProviderId(Connection connection, String providerId) throws Exception {
        int count=0;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        if(connection!=null){
            String sql = "select count(1) as billCount from smbms_bill where" +
                    "	providerId = ?";
            Object []params = {providerId};
            resultSet = baseDao.execute(connection,preparedStatement,resultSet,sql,params);
            if(resultSet.next()){
                count=resultSet.getInt("billCount");
            }
            baseDao.close(null,preparedStatement,null);
        }
        return count;
    }

    //根据用户名或者角色查询用户总数
    public int getBillCount(Connection connection, String productName, int ProviderId,int isPayment) throws SQLException {
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        int count=0;

        if(connection!=null){
            StringBuffer sql= new StringBuffer();
            sql.append("select count(1) as count from smbms_bill b where 1=1");
            ArrayList<Object> list = new ArrayList<Object>();//存放参数


            if(ProviderId>0){
                sql.append(" and providerId = ?");
                list.add(ProviderId);//index:1
            }
            if(isPayment>0){
                sql.append(" and b.isPayment= ?");
                list.add(isPayment);
            }
            if(!StringUtils.isNullOrEmpty(productName)){
                sql.append(" and b.productName = ?");
                list.add("%"+productName+"%");//index:0
            }

            //把list转为数组
            Object[] params = list.toArray();
            System.out.println("sql(count):"+sql.toString());
            for (Object param : params) {
                System.out.println(param);
            }

            resultSet = baseDao.execute(connection, preparedStatement, resultSet, sql.toString(), params);

            if(resultSet.next()){
                count=resultSet.getInt("count");  //从结果集中获取最终的数量
            }
            baseDao.close(null,preparedStatement,resultSet);
        }
        return  count;
    }
}
